#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库初始化脚本
用于创建用户表和初始化默认用户
"""

import pymysql
import bcrypt
import json

def load_config():
    """加载配置文件"""
    with open('config.json', 'r') as f:
        return json.load(f)

def create_database_and_tables():
    """创建数据库和表"""
    config = load_config()
    mysql_config = config['mysql']
    
    print("🔧 开始初始化数据库...")
    
    try:
        # 连接MySQL服务器（不指定数据库）
        connection = pymysql.connect(
            host=mysql_config['host'],
            port=mysql_config['port'],
            user=mysql_config['username'],
            password=mysql_config['password'],
            charset='utf8mb4'
        )
        
        with connection.cursor() as cursor:
            # 创建数据库
            print(f"📋 创建数据库: {mysql_config['database']}")
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS {mysql_config['database']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            
            # 使用数据库
            cursor.execute(f"USE {mysql_config['database']}")
            
            # 创建用户表
            print("👥 创建用户表...")
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
                    password VARCHAR(255) NOT NULL COMMENT '密码（加密存储）',
                    email VARCHAR(100) COMMENT '邮箱',
                    full_name VARCHAR(100) COMMENT '全名',
                    is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否激活',
                    is_admin TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否管理员',
                    last_login TIMESTAMP NULL COMMENT '最后登录时间',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
                )
            """)
            
            # 创建MongoDB连接配置表
            print("🔗 创建MongoDB连接配置表...")
            cursor.execute(f"""
                CREATE TABLE IF NOT EXISTS {mysql_config['table']} (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(100) NOT NULL COMMENT '连接名称',
                    host VARCHAR(255) NOT NULL COMMENT 'MongoDB主机地址',
                    port INT NOT NULL DEFAULT 27017 COMMENT 'MongoDB端口',
                    username VARCHAR(100) NOT NULL COMMENT '用户名',
                    password VARCHAR(255) NOT NULL COMMENT '密码',
                    auth_database VARCHAR(100) NOT NULL DEFAULT 'admin' COMMENT '认证数据库',
                    use_ssl TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否使用SSL',
                    is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否激活',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
                )
            """)
            
            # 创建默认用户
            print("🔐 创建默认用户...")
            
            # 管理员用户 (密码: admin123)
            print("🔐 生成管理员密码哈希...")
            admin_password = bcrypt.hashpw('admin123'.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
            print(f"   管理员密码哈希: {admin_password[:50]}...")
            
            cursor.execute("""
                INSERT INTO users (username, password, email, full_name, is_admin) 
                VALUES (%s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE 
                    password = VALUES(password),
                    email = VALUES(email),
                    full_name = VALUES(full_name),
                    is_admin = VALUES(is_admin),
                    updated_at = CURRENT_TIMESTAMP
            """, ('admin', admin_password, 'admin@example.com', '系统管理员', 1))
            
            # 普通用户 (密码: user123)
            print("🔐 生成普通用户密码哈希...")
            user_password = bcrypt.hashpw('user123'.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
            print(f"   普通用户密码哈希: {user_password[:50]}...")
            
            cursor.execute("""
                INSERT INTO users (username, password, email, full_name, is_admin) 
                VALUES (%s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE 
                    password = VALUES(password),
                    email = VALUES(email),
                    full_name = VALUES(full_name),
                    is_admin = VALUES(is_admin),
                    updated_at = CURRENT_TIMESTAMP
            """, ('user', user_password, 'user@example.com', '普通用户', 0))
            
            # 插入示例MongoDB连接
            print("🔗 创建示例MongoDB连接...")
            cursor.execute(f"""
                INSERT INTO {mysql_config['table']} (name, host, port, username, password, auth_database, use_ssl, is_active) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE 
                    host = VALUES(host),
                    port = VALUES(port),
                    username = VALUES(username),
                    password = VALUES(password),
                    auth_database = VALUES(auth_database),
                    use_ssl = VALUES(use_ssl),
                    updated_at = CURRENT_TIMESTAMP
            """, ('默认连接', '10.16.2.79', 32616, 'admin', '123456', 'admin', 0, 1))
            
            connection.commit()
            
        connection.close()
        
        print("✅ 数据库初始化完成！")
        print("\n📋 默认用户账号:")
        print("   管理员: admin / admin123")
        print("   普通用户: user / user123")
        print("\n🚀 现在可以启动应用了: python app.py")
        
    except Exception as e:
        print(f"❌ 数据库初始化失败: {e}")
        return False
    
    return True

if __name__ == '__main__':
    create_database_and_tables()